﻿using System;
using System.Collections.Generic;
using System.Web.UI.WebControls;
using Microsoft.SharePoint.WebControls;
using System.Data;
using System.Data.SqlClient;

namespace referrals.Layouts.referrals
{
    public partial class Open : LayoutsPageBase
    {
        //protected GridView gridView1 = new GridView();
        private const string SortDirection = "DateReferred DESC";

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                if (Request.QueryString["uid"] != "" && Request.QueryString["uid"] != null)
                {
                    string strClient = "SELECT [UID], [NHSNumber],[Title],[FirstName],[MiddleName],[LastName],[DOB],[Gender],[Ethnicity],[Area],[Location] FROM [referrals].[dbo].[Clients] WHERE (((UID)='" + Request.QueryString["uid"] + "')) ORDER BY UID DESC";
                    //string strReferrals = "SELECT Contacts.ClientID, Contacts.ReasonReferred, Contacts.DateReferred, Contacts.ReferredBy, Contacts.Description, Contacts.Urgency, Contacts.Type, Contacts.DNW, Contacts.ContactBy1, Contacts.ContactBy2, Contacts.AssessmentDate, Contacts.ReportFeedback, Contacts.ReportSent, Contacts.referralResponse, Contacts.DNW, Contacts.RefOutcome, Contacts.Closed, Contacts.RefDiscussion, Contacts.RefDiscussion, Contacts.FOutcome, Contacts.PapersLocation FROM Contacts WHERE (((Contacts.ClientID)='" + Request.QueryString["uid"] + "')) ORDER BY Contacts.DateReferred DESC;";
                    using (SqlConnection conn = new SqlConnection(Connection.GetConnStrSql()))
                    {
                        //SqlConnection conn = connection.GetConnection();
                        SqlCommand sqlCommand = new SqlCommand(strClient, conn);
                        conn.Open();

                        SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
                        DataSet ds = new DataSet();
                        da.Fill(ds);

                        //grdReferralDetails.RowDataBound += new GridViewRowEventHandler(GridView1_RowDataBound);

                        using (ds)
                        {
                            gridView2.DataSource = ds;
                            gridView2.DataBind();

                            DataRow dr = ds.Tables[0].Rows[0];
                            string gender = dr["Gender"].ToString();
                            if (gender == "Female")
                            {
                                gridView2.BackColor = System.Drawing.Color.PaleVioletRed;
                                gridView2.ForeColor = System.Drawing.Color.White;
                            }
                            else
                            {
                                gridView2.BackColor = System.Drawing.Color.Blue;
                                gridView2.ForeColor = System.Drawing.Color.White;
                            }
                        }
                    }

                    ViewState["SortExpr"] = SortDirection;
                    DataView dvContacts = Getdata();
                    gridView3.DataSource = dvContacts;
                    gridView3.DataBind();
                }   
            }
        }

        private DataSet GetData(string query)
        {
            SqlCommand cmd = new SqlCommand(query);
            using (SqlConnection conn = new SqlConnection(Connection.GetConnStrSql()))
            {
                using (SqlDataAdapter sda = new SqlDataAdapter())
                {
                    cmd.Connection = conn;
                    sda.SelectCommand = cmd;
                    using (DataSet ds = new DataSet())
                    {
                        sda.Fill(ds);
                        return ds;
                    }
                }
            }
        }

        private DataView Getdata()
        {
            using (SqlConnection conn = new SqlConnection(Connection.GetConnStrSql()))       
            {
                DataSet dsContactDetails = new DataSet();
                string strSelectCmd = "SELECT Contacts.ClientID, Contacts.ReasonReferred, Contacts.DateReferred, Contacts.ReferredBy, Contacts.Description, Contacts.Urgency, Contacts.Type, Contacts.DNW, Contacts.ContactBy1, Contacts.ContactBy2, Contacts.AssessmentDate, Contacts.ReportFeedback, Contacts.ReportSent, Contacts.referralResponse, Contacts.DNW, Contacts.RefOutcome, Contacts.Closed, Contacts.RefDiscussion, Contacts.RefDiscussion, Contacts.FOutcome, Contacts.PapersLocation FROM Contacts WHERE (((Contacts.ClientID)='" + Request.QueryString["uid"] + "')) ORDER BY Contacts.DateReferred DESC;";
                SqlDataAdapter da = new SqlDataAdapter(strSelectCmd, conn);
                da.Fill(dsContactDetails, "Contacts");
                DataView dvEmp = dsContactDetails.Tables["Contacts"].DefaultView;
                dvEmp.Sort = ViewState["SortExpr"].ToString();
                return dvEmp;
            }
        }

      
        
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void OnRowDataBound(object sender, GridViewRowEventArgs e)
        {
            if (e.Row.RowType == DataControlRowType.DataRow)
            {
                //This is for the Referral reason dropdown
                DropDownList ddlReasons = (e.Row.FindControl("ddlReasons") as DropDownList);
                if (ddlReasons != null)
                {
                    ddlReasons.DataSource = GetData("select * from Picklists where type = 'Reason'");
                    ddlReasons.DataTextField = "Description";
                    ddlReasons.DataValueField = "Code";
                    ddlReasons.DataBind();

                    //Add Default Item in the DropDownList
                    ddlReasons.Items.Insert(0, new ListItem("Please select"));
                }

                //Select the Country of Customer in DropDownList
                var label2 = e.Row.FindControl("lblReasons") as Label;
                if (label2 != null)
                {
                    string reason = label2.Text;
                    if (ddlReasons != null) ddlReasons.Items.FindByValue(reason).Selected = true;
                }

                //This is for the Referrals Type dropdown
                //***************************************
                //Find the DropDownList in the Row
                DropDownList ddlCpaStage = (e.Row.FindControl("ddlCpaStage") as DropDownList);
                if (ddlCpaStage != null)
                {
                    ddlCpaStage.DataSource = GetData("SELECT Picklists.Code, Picklists.Description, Picklists.Type FROM Picklists WHERE (((Picklists.Type)='stage')) OR (((Picklists.Code)=167 Or (Picklists.Code)=168));");
                    ddlCpaStage.DataTextField = "Description";
                    ddlCpaStage.DataValueField = "Code";
                    ddlCpaStage.DataBind();

                    //Add Default Item in the DropDownList
                    ddlCpaStage.Items.Insert(0, new ListItem("Please select"));
                }

                //Select the type of referral in DropDownList
                var label1 = e.Row.FindControl("lblCpaStage") as Label;
                if (label1 != null)
                {
                    string type = label1.Text;
                    if (ddlCpaStage != null) ddlCpaStage.Items.FindByValue(type).Selected = true;
                }
                //****************************************************

                //******** Begin Urgency DropdowlList **************
                DropDownList ddlUrgency = (e.Row.FindControl("ddlUrgency") as DropDownList);
                if (ddlUrgency != null)
                {

                    List<ListItem> items = new List<ListItem>
                        {
                            new ListItem("Emergency", "Emergency"),
                            new ListItem("Routine", "Routine"),
                            new ListItem("Urgent", "Urgent")
                        };
                    items.Sort((item1, item2) => String.Compare(item1.Text, item2.Text, StringComparison.Ordinal));
                    ddlUrgency.Items.AddRange(items.ToArray());

                    var label = e.Row.FindControl("lblUrgency") as Label;
                    if (label != null)
                    {
                        string urgency = label.Text;
                        ddlUrgency.Items.FindByValue(urgency).Selected = true;
                    }
                }

                //************ begin Referred By DropDownList *********
                DropDownList ddlReferredBy = (e.Row.FindControl("ddlReferredBy") as DropDownList);
                if (ddlReferredBy != null)
                {

                    ddlReferredBy.DataSource = GetData("SELECT * FROM referral;");
                    ddlReferredBy.DataTextField = "Organisation";
                    ddlReferredBy.DataValueField = "RefID";
                    ddlReferredBy.DataBind();

                    //Add Default Item in the DropDownList
                    ddlReferredBy.Items.Insert(0, new ListItem("Please select"));

                    var label = e.Row.FindControl("lblReferredBy") as Label;
                    if (label != null)
                    {
                        string referredby = label.Text;
                        ddlReferredBy.Items.FindByValue(referredby).Selected = true;
                    }
                }

                //************ End Referred By DropDownList *********

                
                //************ begin Initial Outcome By DropDownList *********
                DropDownList ddlInitialOutcome = (e.Row.FindControl("ddlInitialOutcome") as DropDownList);
                if (ddlInitialOutcome != null)
                {

                    ddlInitialOutcome.DataSource = GetData("SELECT Picklists.Code, Picklists.Description, Picklists.Type FROM Picklists WHERE (((Picklists.Type)='outcome')) ORDER BY Picklists.Description;");
                    ddlInitialOutcome.DataTextField = "Description";
                    ddlInitialOutcome.DataValueField = "Code";
                    ddlInitialOutcome.DataBind();

                    //Add Default Item in the DropDownList
                    ddlInitialOutcome.Items.Insert(0, new ListItem("Please select"));

                    var label = e.Row.FindControl("lblInitialOutcome") as Label;
                    if (label != null)
                    {
                        string initialoutcome = label.Text;
                        if (initialoutcome != null && initialoutcome.Length >= 1)
                        {
                            if (ddlInitialOutcome.Items.FindByValue(initialoutcome) != null)
                            {
                                ddlInitialOutcome.Items.FindByValue(initialoutcome).Selected = true;
                            }
                        } 
                    }
                }

                //************ begin Final Outcome By DropDownList *********
                DropDownList ddlFinalOutcome = (e.Row.FindControl("ddlFinalOutcome") as DropDownList);
                if (ddlFinalOutcome != null)
                {
                    ddlFinalOutcome.DataSource = GetData("SELECT Picklists.Code, Picklists.Description, Picklists.Type FROM Picklists WHERE (((Picklists.Type)='Foutcome')) ORDER BY Picklists.Description;");
                    ddlFinalOutcome.DataTextField = "Description";
                    ddlFinalOutcome.DataValueField = "Code";
                    ddlFinalOutcome.DataBind();

                    //Add Default Item in the DropDownList
                    ddlFinalOutcome.Items.Insert(0, new ListItem("Please select"));

                    var label = e.Row.FindControl("lblFinalOutcome") as Label;
                    if (label != null)
                    {
                        string finaloutcome = label.Text;
                        if (finaloutcome != null && finaloutcome.Length >= 1)
                        {
                            if (ddlFinalOutcome.Items.FindByValue(finaloutcome) != null)
                            {
                                ddlFinalOutcome.Items.FindByValue(finaloutcome).Selected = true; 
                            }                            
                        } 
                    }
                }

                // ******************** Begin Allocated to Dropdown 1
                DropDownList ddlContactBy1 = (e.Row.FindControl("ddlContactBy1") as DropDownList);
                if (ddlContactBy1 != null)
                {
                    ddlContactBy1.DataSource = GetData("SELECT * from staff;");
                    ddlContactBy1.DataTextField = "Name";
                    ddlContactBy1.DataValueField = "StaffId";
                    ddlContactBy1.DataBind();

                    //Add Default Item in the DropDownList
                    ddlContactBy1.Items.Insert(0, new ListItem("Please select"));

                    var label = e.Row.FindControl("lblContactBy1") as Label;
                    if (label != null)
                    {
                        string contactby1 = label.Text;
                        if (contactby1 != null && contactby1.Length >= 1)
                        {
                            if (ddlContactBy1.Items.FindByValue(contactby1) != null)
                            {
                                ddlContactBy1.Items.FindByValue(contactby1).Selected = true;
                            }
                        }
                    }
                }

                // ******************** Begin Allocated to Dropdown 2
                DropDownList ddlContactBy2 = (e.Row.FindControl("ddlContactBy2") as DropDownList);
                if (ddlContactBy2 != null)
                {
                    ddlContactBy2.DataSource = GetData("SELECT * from staff;");
                    ddlContactBy2.DataTextField = "Name";
                    ddlContactBy2.DataValueField = "StaffId";
                    ddlContactBy2.DataBind();

                    //Add Default Item in the DropDownList
                    ddlContactBy2.Items.Insert(0, new ListItem("Please select"));

                    var label = e.Row.FindControl("lblContactBy2") as Label;
                    if (label != null)
                    {
                        string contactby2 = label.Text;
                        if (contactby2 != null && contactby2.Length >= 1)
                        {
                            if (ddlContactBy2.Items.FindByValue(contactby2) != null)
                            {
                                ddlContactBy2.Items.FindByValue(contactby2).Selected = true;
                            }
                        }
                    }
                }

                // ***** Begin Date Referred (Date) ******
                TextBox txtDateReferred = (e.Row.FindControl("txtDateReferred") as TextBox);
                if (txtDateReferred != null)
                {

                    var label = (e.Row.FindControl("lblDateReferred") as Label);
                    if (label != null && label.Text.Length >= 1)
                    {
                        txtDateReferred.Text = Convert.ToDateTime(label.Text).ToString("dd/MM/yy");
                    }
                }

                // ***** Begin Response Sent (Date) ******
                TextBox txtResponseSent = (e.Row.FindControl("txtResponseSent") as TextBox);
                if (txtResponseSent != null)
                {

                    var label = (e.Row.FindControl("lblreferralResponse") as Label);
                    if (label != null && label.Text.Length >= 1)
                    {
                        txtResponseSent.Text = Convert.ToDateTime(label.Text).ToString("dd/MM/yy");
                    }
                }

                // ***** Begin Report Sent (Date) ******
                TextBox txtReportSent = (e.Row.FindControl("txtReportSent") as TextBox);
                if (txtReportSent != null)
                {

                    var label = (e.Row.FindControl("lblReportSent") as Label);
                    if (label != null && label.Text.Length >= 1)
                    {
                        txtReportSent.Text = Convert.ToDateTime(label.Text).ToString("dd/MM/yy");
                    }
                }

                // ***** Begin Assessment Date ******
                TextBox txtAssessmentDate = (e.Row.FindControl("txtAssessmentDate") as TextBox);
                if (txtAssessmentDate != null)
                {

                    var label = (e.Row.FindControl("lblAssessmentDate") as Label);
                    if (label != null && label.Text.Length >= 1)
                    {
                        txtAssessmentDate.Text = Convert.ToDateTime(label.Text).ToString("dd/MM/yy");
                    }
                }

                //****** Begin Status
                CheckBox chkStatus = (e.Row.FindControl("chkStatus") as CheckBox);
                if (chkStatus != null)
                {
                    var label = (e.Row.FindControl("lblStatus") as Label);
                    if (label != null)
                    {
                        if (label.Text == "True")
                        {
                            chkStatus.Checked = true;
                        }
                    }
                }


                
            }

        }

        protected void btnSearch_Click(object sender, EventArgs e)
        {
            // Make sure either text boxes are filled in 
            if (String.IsNullOrEmpty(txtNHSNumber.Text) && String.IsNullOrEmpty(txtNameSearch.Text)) return;
            string strSelect;

            if (String.IsNullOrEmpty(txtNHSNumber.Text))
            {
                //NHS Number not provideed, so search name
                strSelect = "select uid, NHSNumber, Title, FirstName, MiddleName, LastName, DOB, Gender, Ethnicity, Area, Location  from clients where FirstName like '%" + txtNameSearch.Text +
                            "%' or MiddleName like '%" + txtNameSearch.Text + "%' or LastName like '%" +
                            txtNameSearch.Text + "%'";
            }

            else if (String.IsNullOrEmpty(txtNameSearch.Text))
            {
                //Name not provided so search only NHS Number
                strSelect = "select uid, NHSNumber, Title, FirstName, MiddleName, LastName, DOB, Gender, Ethnicity, Area, Location from clients where NHSNumber like '%" + txtNHSNumber.Text + "%'";
            }
            else
            {
                //Both name and NHS Number provided
                strSelect = "select Uid, NHSNumber, Title, FirstName, MiddleName, LastName, DOB, Gender, Ethnicity, Area, Location from clients where FirstName like '%" + txtNameSearch.Text +
                            "%' or MiddleName like '%" + txtNameSearch.Text + "%' or LastName like '%" +
                            txtNameSearch.Text + "%' and NHSNumber like '_" + txtNHSNumber.Text + "_'";
            }

            using (SqlConnection conn = new SqlConnection(Connection.GetConnStrSql()))
            {
                SqlCommand sqlCommand = new SqlCommand(strSelect, conn);
                conn.Open();
                SqlDataReader reader = sqlCommand.ExecuteReader();
                gridView1.RowDataBound += GridView1_RowDataBound;

                gridView1.DataSource = reader;
                gridView1.DataBind();
            }

        }

        protected void gridView3_PageIndexChanging(object sender, GridViewPageEventArgs e)
        {
            gridView3.PageIndex = e.NewPageIndex;
            DataView dvContacts = Getdata();
            gridView3.DataSource = dvContacts;
            gridView3.DataBind();
        }


        protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            // comments go here
            e.Row.Cells[0].Visible = false;
            if (e.Row.RowType == DataControlRowType.DataRow)
            {
                HyperLink myLink = new HyperLink();
                myLink.Text = e.Row.Cells[1].Text;
                e.Row.Cells[1].Controls.Add(myLink);
                myLink.NavigateUrl = "open.aspx?uid=" + e.Row.Cells[0].Text;
            }
        }


        protected void btnClear_Click(object sender, EventArgs e)
        {
            Response.Redirect("open.aspx");
            //ClearInputs(Page.Controls);
        }

        protected string GetAge(DateTime birthday)
        {  
            int years = new DateTime(DateTime.Now.Subtract(birthday).Ticks).Year - 1;
            return String.Format("Age: {0} Year(s)", years);
        }
    }
}
